package data.TeachPlanData;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import po.coursePO.CoursePO;
import po.teachPlanPO.TeachPlanPO;
import dataservice.ResultMessage;
import dataservice.teachPlanDataService.TeachPlanDatabaseService;

public class TeachPlanData extends UnicastRemoteObject implements
		TeachPlanDatabaseService {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public TeachPlanData() throws RemoteException {
		super();
	}

	@Override
	public ArrayList<TeachPlanPO> getPlanBySemester(String semester,String department)
			throws RemoteException {
		ArrayList<TeachPlanPO> teachPlanPOs = new ArrayList<TeachPlanPO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT teachplan.* ";
			sqlString += "FROM teachplan ";
			sqlString += "WHERE teachplan.semester='" + semester + "' AND teachplan.departName='" +department +"'";
			System.out.println(sqlString);
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				TeachPlanPO teachPlanPO = new TeachPlanPO(semester,
						rs.getString(1), rs.getString(3));
				teachPlanPOs.add(teachPlanPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return teachPlanPOs;
	}

	@Override
	public ArrayList<CoursePO> getUnselectedCourse(String department) throws RemoteException {
		ArrayList<CoursePO> coursePOs = new ArrayList<CoursePO>();
		CoursePO coursePO = null;
		String couID = null;
		String couName = null;
		int credit = 0;
		int courseperiod = 0;
		String classroom = null;
		String assist = null;
		String teaId = null;
		String departName = null;
		String attribute = null;
		String book = null;
		String introduction = null;
		String courseTime = null;
		String teaName = null;
		int maxStudent=0;
		int term;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT course.* ";
			sqlString += "FROM course ";
			sqlString += "WHERE (course.couID not in (SELECT teachplan.couID FROM teachplan WHERE teachplan.departName='"+department+"')) AND (course.attribute='学科专业' OR course.attribute='通识通修') order by departName";
			System.out.println(sqlString);
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				couID = rs.getString(1);
				couName = rs.getString(2);
				credit = rs.getInt(3);
				courseperiod = rs.getInt(4);
				classroom = rs.getString(5);
				assist = rs.getString(6);
				teaId = rs.getString(7);
				departName = rs.getString(8);
				attribute = rs.getString(9);
				book = rs.getString(10);
				introduction = rs.getString(11);
				courseTime = rs.getString(12);
				maxStudent =rs.getInt(13);
				term = rs.getInt(14);
				coursePO = new CoursePO(couID, couName, teaId, teaName, credit,
						courseperiod, classroom, assist, courseTime, attribute,
						maxStudent,term, departName, book, introduction);
				coursePOs.add(coursePO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return coursePOs;
	}

	@Override
	public ArrayList<TeachPlanPO> getPlanByDepartment(String department)
			throws RemoteException {
		ArrayList<TeachPlanPO> teachPlanPOs = new ArrayList<TeachPlanPO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT teachplan.* ";
			sqlString += "FROM teachplan ";
			sqlString += "WHERE teachplan.departName='" + department + "'";
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				TeachPlanPO teachPlanPO = new TeachPlanPO(rs.getString(2),
						rs.getString(1), department);
				teachPlanPOs.add(teachPlanPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return teachPlanPOs;
	}

	@Override
	public ResultMessage insert(TeachPlanPO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String head = "INSERT INTO `选课管理系统`.`teachplan`";
			String colomnString = "(`couID`,`semester`,`departName`)";
			String dataString = "VALUES (" + getValue(po.getCourseID());
			dataString = dataString + "," + getValue(po.getSemester());
			dataString = dataString + "," + getValue(po.getDepartment()) + ")";
			String sqlString = head + colomnString + dataString;
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1062) {
				return ResultMessage.INSERT_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1364) {
				return ResultMessage.INSERT_ISNULL;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.INSERT_SUCCESS;
		} else {
			return ResultMessage.INSERT_FAILURE;
		}
	}

	private String getValue(String valueString) {
		String result = null;
		if (!((valueString == null) || (valueString.isEmpty()))) {
			result = "'" + valueString + "'";
		} else {
			result = "NULL";
		}
		return result;
	}

	@Override
	public CoursePO find(String id) throws RemoteException {
		CoursePO coursePO = null;
		String couID = null;
		String couName = null;
		int credit = 0;
		int courseperiod = 0;
		String classroom = null;
		String assist = null;
		String teaId = null;
		String departName = null;
		String attribute = null;
		String book = null;
		String introduction = null;
		String courseTime = null;
		String teaName = null;
		int term=0;
		int maxStudent=0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT course.* ";
			sqlString += "FROM course";
			sqlString += " WHERE course.couID='" + id + "'";
			System.out.println(sqlString);
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				couID = rs.getString(1);
				couName = rs.getString(2);
				credit = rs.getInt(3);
				courseperiod = rs.getInt(4);
				classroom = rs.getString(5);
				assist = rs.getString(6);
				teaId = rs.getString(7);
				departName = rs.getString(8);
				attribute = rs.getString(9);
				book = rs.getString(10);
				introduction = rs.getString(11);
				courseTime = rs.getString(12);
				maxStudent=rs.getInt(13);
				term = rs.getInt(14);
				coursePO = new CoursePO(couID, couName, teaId, teaName, credit,
						courseperiod, classroom, assist, courseTime, attribute,
						maxStudent, term, departName, book, introduction);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return coursePO;
	}

	@Override
	public ResultMessage delete(TeachPlanPO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = 
					"DELETE FROM `选课管理系统`.`teachplan` "
					+ "WHERE `couID` = '" + po.getCourseID() 
					+ "' AND `semester`='" + po.getSemester()
					+ "' AND `departName`='"+ po.getDepartment() +"'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.DELETE_SUCCESS;
		} else {
			return ResultMessage.DELETE_FAILURE;
		}
	}
}
